﻿using BySoft.Server.Common;
using BySoft.Server.Common.Entity;
using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess
{
    public partial class BsDeliveryorderDA
    {
        public static DataTable SelectList(string strBillID,string strProductID, string strWorkOrderNo, string strFrom, string strEnd, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"select  a.*,
   d.DetailID, d.WorkOrderNo, d.ProductID, d.Nums, d.Amount, d.Remark,
   p.ProductName,p.Specifications,p.Unit,p.CustomerPartNo,p.OutSide,p.InSide,
   pt.ProductTypeName,
   mc.CustomerName,mc.Addr, 

  -1 as IsNew,-1 as IsDel
from Bs_DeliveryOrder a
LEFT JOIN BS_DeliveryOrderDetail AS d ON d.BillID = a.BillID
LEFT JOIN MD_Customer AS mc ON  mc.CustomerID=a.CustomerID

LEFT JOIN md_Product p ON d.ProductID=p.ProductID
LEFT JOIN md_ProductType pt ON p.ProductTypeID=pt.ProductTypeID

 WHERE a.[Status]='1' ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and a.BillID like @strBillID";
                    dpc.AddWithValue("@strBillID", "%" + strBillID + "%");
                }

                if (!string.IsNullOrEmpty(strProductID))
                {
                    strcmd += " and d.ProductID like @strProductID";
                    dpc.AddWithValue("@strProductID", "%" + strProductID + "%");
                }
                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and a.SupplierID Like @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%" + strWorkOrderNo +"%" );
                }

                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and a.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strEnd))
                {
                    strcmd += " and a.BillDate <= @strEnd";
                    dpc.AddWithValue("@strEnd", strEnd );
                }
                strcmd += " order by a.BillDate desc";



                DataTable  dt = DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
         
               
               return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// 排程单中没有结案的
        /// 
        /// </summary>
      
        /// <returns></returns>
        public static DataTable SelectDialogList(string strCurrentID, string strProductName, string strOrderID,
            string strCustomerID, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"select Row_number() OVER (ORDER by bod.ProductID) AS rowNumber,
   bod.OrderID, bod.ProductID, bod.Price, 
   p.ProductName,p.Specifications,p.Unit,
   pt.ProductTypeName,p.CustomerPartNo,p.OutSide,p.InSide,
   bsd.ScheduleID,bsd.ScheduleDetailID,
   bsd.WorkOrderNo,bsd.PlanID,
   bsd.ScheduleNum,bsd.ExpectInStockDate, bsd.Remark,
   mc.CustomerName,mc.Addr, 
   isnull(ret.ReturnNums,0) AS ReturnNum ,bod.Nums AS OrderNum,
   bod.Nums-isnull(ret.ReturnNums,0) as LastNum,

  -1 as IsNew,-1 as IsDel
FROM   

BS_Schedule AS bs 

   left join BS_ScheduleDetail AS bsd ON bsd.ScheduleID = bs.ScheduleID
LEFT JOIN BS_OrderDetail AS bod ON bod.WorkOrderNo=bsd.WorkOrderNo
LEFT JOIN Bs_order AS bo ON bo.OrderID = bod.OrderID

LEFT JOIN MD_Customer AS mc ON  mc.CustomerID=bo.CustomerID
LEFT JOIN md_Product p ON bod.ProductID=p.ProductID
LEFT JOIN md_ProductType pt ON p.ProductTypeID=pt.ProductTypeID
 LEFT JOIN  (
        
        ---已有数量
          SELECT ProductID,sum(Nums) as ReturnNums 
            from Bs_DeliveryOrderDetail aR 
           -- where aR.BillID<>@CurrentBillID
             group by  ProductID
             )  ret on ret.ProductID=bod.ProductID 
             
WHERE isnull(ret.ReturnNums,0)< bod.Nums  --未达数量


   ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();


                dpc.AddWithValue("@CurrentBillID", strCurrentID);

                if (!string.IsNullOrEmpty(strOrderID))
                {
                    strcmd += " and bod.OrderID like  @strOrderID";
                    dpc.AddWithValue("@strOrderID", "%"+strOrderID+"%");
                }

                if (!string.IsNullOrEmpty(strProductName))
                {
                    strcmd += " and p.ProductName like @strProductName";
                    dpc.AddWithValue("@strProductName", "%" + strProductName + "%");
                }

                if (!string.IsNullOrEmpty(strCustomerID))
                {
                    strcmd += " and mc.CustomerID = @strCustomerID";
                    dpc.AddWithValue("@strCustomerID", strCustomerID);
                }


                strcmd += " order by p.ProductID desc";



                DataTable dt = DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
               // AddRowIndex(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private static void AddRowIndex(DataTable dt)
        {
            dt.Columns.Add("RowIndex");
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                dr["RowIndex"] = i++;
            }
        }



      
        #region View Data
        public static DataTable SelectHeadData(string strBillID)
        {
            try
            {
                string strcmd = @"SELECT a.*,                           
    
   mc.CustomerName,mc.Addr,mc.MasterPerson,
   0 as RowNumber,0.0000 as Price,
   cd.ContactName,cd.Mobile,
                                
-1 as IsNew,-1 as IsDel
from Bs_DeliveryOrder a

LEFT JOIN MD_Customer AS mc ON  mc.CustomerID=a.CustomerID
 --left join MD_CustomerDetail cd on cd.CustomerID=mc.CustomerID
 
 
LEFT JOIN (select cd.ContactName,cd.Mobile,cd.Tel,cd.Job,cd.Email,cd.QQ,c.CustomerID,c.CustomerDetailID from 
( select c.CustomerID,min(cd.CustomerDetailID) CustomerDetailID from MD_Customer c
                                    left join MD_CustomerDetail cd on cd.CustomerID=c.CustomerID
                                    group by c.CustomerID) c
         left join MD_CustomerDetail cd on cd.CustomerDetailID=c.CustomerDetailID) cd
          on a.CustomerID=cd.CustomerID 
where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                DataTable dt= DataAccessCommon.Select(strcmd, dpc).Tables[0];
                return  dt ;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

       public static DataTable SelectDetailData(string strBillID)
        {
            try
            {
                string strcmd = @"SELECT a.*,                           
     p.ProductName,p.Specifications,p.Unit,
     p.CustomerPartNo,p.OutSide,p.InSide,
   a.nums as LastNum, 
   0 as RowNumber,0.0000 as Price,
-1 as IsNew,-1 as IsDel
from Bs_DeliveryOrderDetail a
LEFT JOIN md_Product p ON a.ProductID=p.ProductID

where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                DataTable dt= DataAccessCommon.Select(strcmd, dpc).Tables[0];
                return  dt ;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        #endregion



        #region AUD
        public static bool SaveOrUpdate(BsDeliveryorderEntity entity, DataTable DeliveryOrderDetailDT ,bool isNew)
        {
            bool istag = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                broker.BeginTransaction();
                try
                {
                    if (isNew)
                    {
                        DataAccessCommon.Insert(entity, broker);
                    }
                    else
                    {
                        DataAccessCommon.UpdateOnly(entity, broker);
                        ClsCommDA.Del("BS_DeliveryorderDetail", "BillID", entity.BillID, broker);//delete  first  & then insert new 
                  

                    }
                    if (DeliveryOrderDetailDT != null && DeliveryOrderDetailDT.Rows.Count > 0)
                    {
                        foreach (DataRow drItem in DeliveryOrderDetailDT.Rows)
                        {
                            BsDeliveryorderdetailEntity DetailEntity = new BsDeliveryorderdetailEntity();
                            DetailEntity.BillID = entity.BillID;
                            DetailEntity.Nums = CommonMethod.ConvertToDecimal(drItem["Nums"]);

                            DetailEntity.Amount = CommonMethod.ConvertToDecimal(drItem["Amount"], 2);
                            DetailEntity.DetailID = CommonMethod.ConvertToDecimal(drItem["DetailID"]);
                            DetailEntity.WorkOrderNo = CommonMethod.GetString(drItem["WorkOrderNo"]);
                            DetailEntity.ProductID = CommonMethod.GetString(drItem["ProductID"]);
                           
                        
                            string strIsNew = CommonMethod.GetString(drItem["IsNew"]);
                            string strIsDel = CommonMethod.GetString(drItem["IsDel"]);
                            if (strIsNew == "1")//insert
                            {
                                DataAccessCommon.Insert(DetailEntity, true, broker);
                            }
                            else
                            {
                                if (strIsDel != "1")//update
                                {
                                    //DetailEntity.DetailID = CommonMethod.ConvertToDecimal(drItem["DetailID"]);
                                    //DataAccessCommon.UpdateOnly(DetailEntity, broker);
                                    DataAccessCommon.Insert(DetailEntity, true, broker);

                                }
                                else if (strIsDel == "1")
                                {
                                    //删除明细  不会用到这里，@See Del()
                                    ClsCommDA.Del("BS_OutmatreturnDetail", "DetailID", CommonMethod.GetString(drItem["DetailID"]), broker);
                                }


                            }

                        }
                    }
                    broker.Commit();
                }
                catch
                {
                    istag = false;
                    broker.Rollback();
                    throw;
                }
                return istag;
            }
        }

        public static bool Del(string strCode)
        {
            bool isResult = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    DataAccessCommon.Delete(strCode, "BillID", "BS_DeliveryOrderDetail");
                    DataAccessCommon.Delete(strCode, "BillID", "BS_DeliveryOrder");
                    broker.Commit();
                }
                catch (Exception ex)
                {
                    isResult = false;
                    broker.Rollback();
                    throw ex;
                }
            }
            return isResult;
        } 
        #endregion
    }
}
